//
//  BIDDatabaseManager.m
//  EducationAssistant
//
//  Created by mal on 15/7/31.
//  Copyright (c) 2015年 cnsoft. All rights reserved.
//

#import "BIDDatabaseManager.h"
#import <sqlite3.h>

@interface BIDDatabaseManager()
{
    sqlite3 *_db;
}
@end
@implementation BIDDatabaseManager

+ (BIDDatabaseManager*)getInstance
{
    static BIDDatabaseManager *db;
    static dispatch_once_t onceToken;
    dispatch_once(&onceToken, ^{
        db = [[BIDDatabaseManager alloc] init];
    });
    return db;
}
/**
 *  打开或新建数据库
 *
 *  @param dbName 数据库名字
 *
 *  @return 数据库打开成功或失败
 */
- (BOOL)openDB
{
    BOOL bFlag = YES;
    NSString *strBundlePath = [[NSBundle mainBundle] pathForResource:@"community" ofType:@"sqlite"];
    NSString *strDBPath = NSSearchPathForDirectoriesInDomains(NSDocumentDirectory, NSUserDomainMask, YES).firstObject;
    strDBPath = [strDBPath stringByAppendingPathComponent:@"community.sqlite"];
    NSFileManager *fm = [NSFileManager defaultManager];
    //
    NSString *strDBConfigPath = [[NSBundle mainBundle] pathForResource:@"dbConfig" ofType:@"plist"];
    NSDictionary *dbDic = [[NSDictionary alloc] initWithContentsOfFile:strDBConfigPath];
    NSNumber *dbVersion = dbDic[@"version"];
    NSUserDefaults *userDefaults = [NSUserDefaults standardUserDefaults];
    NSNumber *curVersion = [userDefaults objectForKey:@"dbVersion"];
    if(![fm fileExistsAtPath:strDBPath])
    {
        [fm copyItemAtPath:strBundlePath toPath:strDBPath error:nil];
    }
    else
    {
        //当前数据库版本和配置文件中的数据库版本不同，则替换
        if(!curVersion || ([dbVersion intValue]!=[curVersion intValue]))
        {
            BOOL bSuccess = [fm removeItemAtPath:strDBPath error:nil];
            bSuccess = [fm copyItemAtPath:strBundlePath toPath:strDBPath error:nil];
        }
    }
    [userDefaults setObject:dbVersion forKey:@"dbVersion"];
    [userDefaults synchronize];
    
    if(sqlite3_open([strDBPath UTF8String], &_db) != SQLITE_OK)
    {
        sqlite3_close(_db);
        NSLog(@"open db failed");
        bFlag = NO;
    }
    return bFlag;
}
/**
 *  关闭数据库
 *
 *  @return 成功or失败
 */
- (BOOL)closeDB
{
    return sqlite3_close(_db);
}
/**
 *  创建数据表
 *
 *  @param tableName 数据表名字
 *
 *  @return 创建数据表成功或失败
 */
- (BOOL)createTable:(NSString *)tableName
{
    char *err;
    NSString *strSql = [[NSString alloc] initWithFormat:@"create table if not exists %@ (id integer primary key autoincrement, name text, img blob)", tableName];
    if(sqlite3_exec(_db, [strSql UTF8String], nil, nil, &err) == SQLITE_OK)
    {
        return YES;
    }
    else
    {
        NSLog(@"create %@ failed.", tableName);
        return NO;
    }
}
/**
 *  删除数据表
 *
 *  @param tableName 数据表名字
 *
 *  @return YES/NO
 */
- (BOOL)deleteTable:(NSString *)tableName
{
    char *err;
    NSString *strSql = [[NSString alloc] initWithFormat:@"drop table %@", tableName];
    if(sqlite3_exec(_db, [strSql UTF8String], nil, nil, &err) == SQLITE_OK)
    {
        return YES;
    }
    else
    {
        return NO;
    }
}
/**
 *  删除数据表中的所有记录
 *
 *  @param tableName 表名字
 *
 *  @return <#return value description#>
 */
- (BOOL)deleteAllFromTable:(NSString *)tableName
{
    char *err;
    NSString *strSql = [[NSString alloc] initWithFormat:@"delete from %@", tableName];
    if(sqlite3_exec(_db, [strSql UTF8String], nil, nil, &err) == SQLITE_OK)
    {
        return YES;
    }
    else
    {
        return NO;
    }
}
/**
 *  插入数据
 *
 *  @param tableName  数据表名字
 *  @param contentDic 要插入的内容
 *
 *  @return <#return value description#>
 *  适用于内容含有<=1个blob类型的情况
 */
- (BOOL)insertRecordToTable:(NSString *)tableName content:(NSDictionary *)contentDic
{
    char *err;
    BOOL isBlob = NO;
    int indexForBlob = 0;
    NSData *data = nil;
    NSArray *keyArr = contentDic.allKeys;
    NSArray *valueArr = contentDic.allValues;
    NSMutableString *strFields = [[NSMutableString alloc] init];
    NSMutableString *strValues = [[NSMutableString alloc] init];
    for(int i=0; i<keyArr.count; i++)
    {
        id obj = valueArr[i];
        if(i==0)
        {
            [strFields appendString:keyArr[0]];
            if([obj isKindOfClass:[NSData class]])
            {
                indexForBlob = i;
                isBlob = YES;
                data = (NSData*)obj;
                [strValues appendString:@"?"];
            }
            else if([obj isKindOfClass:[NSNumber class]])
            {
                [strValues appendFormat:@"%@", obj];
            }
            else
            {
                [strValues appendFormat:@"\'%@\'", obj];
            }
        }
        else
        {
            [strFields appendFormat:@",%@", keyArr[i]];
            if([obj isKindOfClass:[NSData class]])
            {
                indexForBlob = i;
                isBlob = YES;
                data = (NSData*)obj;
                [strValues appendString:@",?"];
            }
            else if([obj isKindOfClass:[NSNumber class]])
            {
                [strValues appendFormat:@",%@", obj];
            }
            else
            {
                [strValues appendFormat:@",\'%@\'", obj];
            }
        }
    }
    NSString *strSql = [[NSString alloc] initWithFormat:@"insert into %@ (%@) values (%@)", tableName, strFields, strValues];
    if(isBlob)
    {
        sqlite3_stmt *stmt;
        sqlite3_prepare(_db, [strSql UTF8String], -1, &stmt, 0);
        sqlite3_bind_blob(stmt, 10, [data bytes], [data length], nil);
        sqlite3_step(stmt);
        sqlite3_finalize(stmt);
        return YES;
    }
    else
    {
        if(sqlite3_exec(_db, [strSql UTF8String], nil, nil, &err) == SQLITE_OK)
        {
            return YES;
        }
        else
        {
            NSLog(@"insert data to %@ failed.Because %@", tableName, [[NSString alloc] initWithCString:err encoding:NSUTF8StringEncoding]);
            return NO;
        }
    }
}
/**
 *  从数据表中删除一条记录
 *
 *  @param tableName    数据表名字
 *  @param conditionDic 查询条件
 *
 *  @return <#return value description#>
 */
- (BOOL)deleteRecordFromTable:(NSString *)tableName condition:(NSDictionary *)conditionDic
{
    NSArray *keyArr = conditionDic.allKeys;
    NSArray *valueArr = conditionDic.allValues;
    NSMutableString *strExpr = [[NSMutableString alloc] init];
    for(int i=0; i<keyArr.count; i++)
    {
        id obj = valueArr[i];
        if(i==0)
        {
            if([obj isKindOfClass:[NSNumber class]])
            {
                [strExpr appendFormat:@"%@=%@", keyArr[i], valueArr[i]];
            }
            else
            {
                [strExpr appendFormat:@"%@=\'%@\'", keyArr[i], valueArr[i]];
            }
        }
        else
        {
            if([obj isKindOfClass:[NSNumber class]])
            {
                [strExpr appendFormat:@"AND %@=%@", keyArr[i], valueArr[i]];
            }
            else
            {
                [strExpr appendFormat:@"AND %@=\'%@\'", keyArr[i], valueArr[i]];
            }
        }
    }
    char *err;
    NSString *strSql = [[NSString alloc] initWithFormat:@"delete from %@ where %@", tableName, strExpr];
    if(sqlite3_exec(_db, [strSql UTF8String], nil, nil, &err) == SQLITE_OK)
    {
        return YES;
    }
    return NO;
}
/**
 *  更新某条记录
 *
 *  @param tableName    数据表名字
 *  @param contentDic   要更新的数据
 *  @param conditionDic 查询条件
 *
 *  @return yes/no
 */
- (BOOL)updateTable:(NSString *)tableName content:(NSDictionary *)contentDic condition:(NSDictionary *)conditionDic
{
    NSMutableString *strAssignExpr = [[NSMutableString alloc] init];
    NSMutableString *strConditionExpr = [[NSMutableString alloc] init];
    NSArray *keyArr = contentDic.allKeys;
    NSArray *valueArr = contentDic.allValues;
    NSObject *obj = nil;
    NSString *strKey = @"";
    BOOL isBlob = NO;
    NSData *data = nil;
    //取得赋值语句
    for(int i=0; i<keyArr.count; i++)
    {
        strKey = keyArr[i];
        obj = valueArr[i];
        if(i==0)
        {
            if([obj isKindOfClass:[NSNumber class]])
            {
                [strAssignExpr appendFormat:@"%@=%@", strKey, obj];
            }
            else if([obj isKindOfClass:[NSData class]])
            {
                isBlob = YES;
                data = (NSData*)obj;
                [strAssignExpr appendFormat:@"%@=?", strKey];
            }
            else
            {
                [strAssignExpr appendFormat:@"%@=\'%@\'", strKey, obj];
            }
        }
        else
        {
            if([obj isKindOfClass:[NSNumber class]])
            {
                [strAssignExpr appendFormat:@",%@=%@", strKey, obj];
            }
            else if([obj isKindOfClass:[NSData class]])
            {
                isBlob = YES;
                [strAssignExpr appendFormat:@",%@=?", strKey];
                data = (NSData*)obj;
            }
            else
            {
                [strAssignExpr appendFormat:@",%@=\'%@\'", strKey, obj];
            }
        }
    }
    //取得条件语句
    keyArr = conditionDic.allKeys;
    valueArr = conditionDic.allValues;
    for(int i=0; i<keyArr.count; i++)
    {
        strKey = keyArr[i];
        obj = valueArr[i];
        if(i==0)
        {
            if([obj isKindOfClass:[NSNumber class]])
            {
                [strConditionExpr appendFormat:@"%@=%@", strKey, obj];
            }
            else
            {
                [strConditionExpr appendFormat:@"%@=\'%@\'", strKey, obj];
            }
        }
        else
        {
            if([obj isKindOfClass:[NSNumber class]])
            {
                [strConditionExpr appendFormat:@" AND %@=%@", strKey, obj];
            }
            else
            {
                [strConditionExpr appendFormat:@" AND %@=\'%@\'", strKey, obj];
            }
        }
    }
    NSString *strSql = [[NSString alloc] initWithFormat:@"update %@ set %@ where %@", tableName, strAssignExpr, strConditionExpr];
    char *err;
    if(isBlob)
    {
        sqlite3_stmt *stmt;
        sqlite3_prepare(_db, [strSql UTF8String], -1, &stmt, 0);
        sqlite3_bind_blob(stmt, 1, [data bytes], [data length], nil);
        sqlite3_step(stmt);
        sqlite3_finalize(stmt);
        return YES;
    }
    else
    {
        if(sqlite3_exec(_db, [strSql UTF8String], nil, nil, &err) == SQLITE_OK)
        {
            return YES;
        }
        else
        {
            NSLog(@"update data to %@ failed.Because %@", tableName, [[NSString alloc] initWithCString:err encoding:NSUTF8StringEncoding]);
            return NO;
        }
    }
}
/**
 *  查询数据,fieldArr和conditionArr分别传@[],@{}传入时，查询所给表的所有记录
 *
 *  @param tableName    数据表名字
 *  @param fieldArr     要查询的字段
 *  @param conditionArr 查询条件
 *  @param index        如果查询出的结果里含有blob类型的数据，则index代表blob类型的数据位于第几列，index=-1代表没有blob类型的数据
 *  @return yes no
 */
- (NSMutableArray*)queryFromTable:(NSString *)tableName fields:(NSArray *)fieldArr condition:(NSDictionary *)conditionDic index:(NSInteger)index
{
    NSMutableArray *resultArr = [[NSMutableArray alloc] init];
    NSMutableString *strFields = [[NSMutableString alloc] init];
    NSArray *keyArr = conditionDic.allKeys;
    NSArray *valueArr = conditionDic.allValues;
    NSMutableString *strExpr = [[NSMutableString alloc] init];
    for(int i=0; i<fieldArr.count; i++)
    {
        if(i==0)
        {
            [strFields appendString:fieldArr[i]];
        }
        else
        {
            [strFields appendFormat:@",%@", fieldArr[i]];
        }
    }
    for(int i=0; i<keyArr.count; i++)
    {
        id obj = valueArr[i];
        if(i==0)
        {
            if([obj isKindOfClass:[NSNumber class]])
            {
                [strExpr appendFormat:@"%@=%@", keyArr[i], valueArr[i]];
            }
            else
            {
                [strExpr appendFormat:@"%@=\'%@\'", keyArr[i], valueArr[i]];
            }
        }
        else
        {
            if([obj isKindOfClass:[NSNumber class]])
            {
                [strExpr appendFormat:@" AND %@=%@", keyArr[i], valueArr[i]];
            }
            else
            {
                [strExpr appendFormat:@" AND %@=\'%@\'", keyArr[i], valueArr[i]];
            }
        }
    }
    NSString *strSql = @"";
    if(strFields.length>0)
    {
        if(strExpr.length>0)
        {
            strSql = [[NSString alloc] initWithFormat:@"select %@ from %@ where %@", strFields, tableName, strExpr];
        }
        else
        {
            strSql = [[NSString alloc] initWithFormat:@"select %@ from %@", strFields, tableName];
        }
    }
    else
    {
        if(strExpr.length>0)
        {
            strSql = [[NSString alloc] initWithFormat:@"select * from %@ where %@", tableName, strExpr];
        }
        else
        {
            strSql = [[NSString alloc] initWithFormat:@"select * from %@", tableName];
        }
    }
    sqlite3_stmt *stmt;
    if(sqlite3_prepare_v2(_db, [strSql UTF8String], -1, &stmt, nil) == SQLITE_OK)
    {
        while(sqlite3_step(stmt) == SQLITE_ROW)
        {
            NSMutableDictionary *dic = [[NSMutableDictionary alloc] init];
            for(int i=0; i<fieldArr.count; i++)
            {
                if(i==index)
                {
                    const char *data = sqlite3_column_blob(stmt, i);
                    int dataLen =  sqlite3_column_bytes(stmt, i);
                    NSData *desData = [[NSData alloc] initWithBytes:data length:dataLen];
                    [dic setObject:desData forKey:fieldArr[i]];
                }
                else
                {
                    const unsigned char *data = sqlite3_column_text(stmt, i);
                    int dataLen = sqlite3_column_bytes(stmt, i);
                    NSString *text = [[NSString alloc] initWithBytes:data length:dataLen encoding:NSUTF8StringEncoding];
                    [dic setObject:text forKey:fieldArr[i]];
                }
            }
            [resultArr addObject:dic];
        }
        sqlite3_finalize(stmt);
    }
    return resultArr;
}
/**
 *  执行sql语句
 *
 *  @param strSQL sql语句
 *
 *  @return 成功或失败
 *  创建表"CREATE TABLE IF NOT EXISTS PERSONINFO (ID INTEGER PRIMARY KEY AUTOINCREMENT, name TEXT, age INTEGER, address TEXT)"
 *  插入数据"INSERT INTO '%@' ('%@', '%@', '%@') VALUES ('%@', '%@', '%@')"
 */
- (BOOL)execSQL:(NSString *)strSQL
{
    char *err;
    if(sqlite3_exec(_db, [strSQL UTF8String], nil, nil, &err) != SQLITE_OK)
    {
        sqlite3_close(_db);
        NSLog(@"exec sql expression failed");
        return NO;
    }
    return YES;
}
/**
 *  判断某个表是否存在
 *
 *  @param tableName 表名字
 *
 *  @return 存在or不存在
 *  "SELECT COUNT(*) FROM sqlite_master where type='table' and name='DBInfo'"
 */
- (BOOL)isTableExist:(NSString *)tableName
{
    //NSString *strSQL = [[NSString alloc] initWithFormat:@"SELECT COUNT(*) FROM sqlite_master whert type='table' and name='%@'", tableName];
    
    return YES;
}


@end
